package com.petsbang.db;

import java.io.Serializable;
import java.util.Date;
import com.petsbang.common.Common;
import com.petsbang.vo.PictureInfo;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
/**
 * 										
 * <pre>										
 * [名 称]：DataBase										
 * [功 能]：数据库封装类										
 * [描 述]：存储图片，获取图片，添加收藏等操作										
 * </pre>										
 *										
 * @author 	lhy									
 * @创建时间  2012-03-11
 */
public class DataBase implements Serializable {

	private static final long serialVersionUID = -7060210544600464481L;
	private static final int DATABASE_VERSION = 1;
	private static final String DATABASE_NAME = "petsbang.db";
	private static final String TABLE_NAME = "picture";
	
	private static final String TAG = "DataBase";

	private Context context;
	private DataBaseOpenHelper dbOpenHelper;
	private SQLiteDatabase sqliteDatabase;
	
	/**
	 * 创建数据库对象
	 * @param context
	 */
	public DataBase(Context context) {
		this.context = context;
	}

	/**
	 * 打开数据库
	 * @return
	 * @throws SQLException
	 */
	public DataBase open() throws SQLException {
		dbOpenHelper = new DataBaseOpenHelper(context);
		return this;
	}

	/**
	 * 关闭数据库
	 * @return
	 * @throws SQLException
	 */
	public void close() {
		dbOpenHelper.close();
		sqliteDatabase.close();
	}


	/**
	 * 保存图片信息
	 * @param user
	 * @return
	 */
	public long insertPictureInfos(String pictureTag,PictureInfo picInfos) {

		ContentValues conValue = new ContentValues();

		conValue.put(Common.ITEM_ID, picInfos.getId());
		if (pictureTag.equals(Common.TAG_HEAVY)) {
			conValue.put("tag",Common.TAG_HEAVY);
		} else if(pictureTag.equals(Common.TAG_MENG)) {
			conValue.put("tag",Common.TAG_MENG);
		} else if(pictureTag.equals(Common.TAG_CARTON)) {
			conValue.put("tag",Common.TAG_CARTON);
		} else {
			conValue.put("tag",Common.TAG_START);
		}
		conValue.put(Common.WIDTH, picInfos.getWidth());
		conValue.put(Common.HEIGHT, picInfos.getHeight());
		conValue.put(Common.IMAGE_WIDTH, picInfos.getImage_width());
		conValue.put(Common.IMAGE_HEIGHT, picInfos.getImage_height());
		conValue.put(Common.LARGE_URL, picInfos.getLarge_url());
		conValue.put(Common.MIDDLE_URL, picInfos.getMiddle_url());
		conValue.put(Common.THUMBNAIL_URL, picInfos.getThumbnail_url());
		conValue.put(Common.SHARE_URL, picInfos.getShare_url());
		conValue.put(Common.TIMESTAMP, picInfos.getTimestamp());
		conValue.put(Common.COMMENTS_COUNT, picInfos.getComments_count());
		conValue.put(Common.DESCRIPTION, picInfos.getDesc());
		conValue.put(Common.FAVOR, 0);
		conValue.put(Common.FAVOR_TIMESTAMP,0);
		
		long id = -1;
		try {
			sqliteDatabase = dbOpenHelper.getWritableDatabase();
			id = sqliteDatabase.insert(TABLE_NAME, null, conValue);
		} catch (Exception e) {
			Log.v(TAG, "error! insertPictureInfos()...");
			e.printStackTrace();
		}
		Log.i(TAG, "success! insertPictureInfos()...");
		
		return id;
	}
	
	/**
	 * 获取图片信息
	 * @return
	 */
	public Cursor queryPictureInfos(String pictureTag) {

		sqliteDatabase = dbOpenHelper.getReadableDatabase();
		Cursor cursor = null;
		try {
			String selection = "tag=?";
			String[] selectionArgs = new String[] { pictureTag };
			String orderBy = "favor_timestamp desc";
			cursor = sqliteDatabase.query(TABLE_NAME, null, selection,
					selectionArgs, null, null, orderBy);
		} catch (Exception e) {
			Log.v(TAG, "error! queryPictureInfos()...");
			e.printStackTrace();
		}

		return cursor;
	}
	
	/**
	 * 收藏图片，将图片favor标志设置为1：收藏，0：取消
	 * @param pictureId 图片id
	 * @param favorFlag 收藏标志，1：收藏，0：取消
	 * @return 更新对应的行编号
	 */
	public int updateFavorPicture(int pictureId, int favorFlag) {
		Date date = new Date();
		long time = date.getTime();
		String timestamp = time + "";
		timestamp = timestamp.substring(0, 10);
		
		ContentValues conValue = new ContentValues();
		conValue.put("favor", favorFlag);
		conValue.put("favor_timestamp", timestamp);
		
		int rowNum = -1;
		try {
			sqliteDatabase = dbOpenHelper.getWritableDatabase();
			rowNum = sqliteDatabase.update(TABLE_NAME, conValue, 
					Common.ITEM_ID + "=" + "'" + pictureId + "'", null);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return rowNum;
	}
	
	/**
	 * 获取图片信息
	 * @param tag 类别
	 * @return
	 */
	public Cursor queryFavorPicture(String tag) {
		// sqlite> select id,timestamp from picture where tag='meng' and favor='1' group by
		// id order by timestamp desc;
		sqliteDatabase = dbOpenHelper.getReadableDatabase();
		Cursor cursor = null;
		try {
			String selection = "tag=? and favor=?";
			String[] selectionArgs = new String[] {tag, 1 + ""};
			String[] cols = new String[] { 
					"timestamp", 
					"id", 
					"tag",
					"width", 
					"height", 
					"image_width", 
					"image_height",
					"large_url",
					"middle_url", 
					"thumbnail_url", 
					"share_url",
					"comments_count", 
					"desc",
					"favor",
					"favor_timestamp"};
			String orderBy = " favor_timestamp desc";
			cursor = sqliteDatabase.query(true,TABLE_NAME, cols, selection,
					selectionArgs, null, null, orderBy, null);
			
		} catch (Exception e) {
			Log.v(TAG, "error! queryFavorPicture()...");
			e.printStackTrace();
		}

		return cursor;
	}

	/****************************************************************************/
	/**
	 * 										
	 * <pre>										
	 * [名 称]：DataBaseOpenHelper,内部类										
	 * [功 能]：创建数据库类										
	 * [描 述]：Nothing										
	 * </pre>										
	 *										
	 * @author 	lhy									
	 * @创建时间  2012-03-11
	 */
	private static class DataBaseOpenHelper extends SQLiteOpenHelper {

		public DataBaseOpenHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		public void onCreate(SQLiteDatabase sdb) {
			sdb.execSQL(createPictrueSQL());
			sdb.execSQL( createTagList());
		}

		public void onUpgrade(SQLiteDatabase sdb, int oldVersion, int newVersion) {
			String sql = "DROP TABLE IF EXISTS " + TABLE_NAME;
			sdb.execSQL(sql);
			String sql2 = "DROP TABLE IF EXISTS " + "tag_list";
			sdb.execSQL(sql2);
			onCreate(sdb);
		}
		
		/**
		 * 创建图片数据库表语句
		 * @return SQL语句
		 */
		private String createPictrueSQL() {
			
			StringBuffer sbfSQL = new StringBuffer();
			sbfSQL.append(" CREATE TABLE picture ( \n");
			sbfSQL.append(" increasing_index INTEGER PRIMARY KEY AUTOINCREMENT,\n");
			sbfSQL.append(" " + Common.TIMESTAMP + " INTEGER ,\n");
			sbfSQL.append(" " + Common.ITEM_ID + " INTEGER ,\n");
			sbfSQL.append(" " + Common.TAG + " VARCHAR ,\n");
			sbfSQL.append(" " + Common.WIDTH +" INTEGER ,\n");
			sbfSQL.append(" " + Common.HEIGHT +" INTEGER ,\n");
			sbfSQL.append(" " + Common.IMAGE_WIDTH +" INTEGER ,\n");
			sbfSQL.append(" " + Common.IMAGE_HEIGHT +" INTEGER ,\n");
			sbfSQL.append(" " + Common.LARGE_URL +" VARCHAR ,\n");
			sbfSQL.append(" " + Common.MIDDLE_URL +" VARCHAR ,\n");
			sbfSQL.append(" " + Common.THUMBNAIL_URL +" VARCHAR ,\n");
			sbfSQL.append(" " + Common.SHARE_URL +" VARCHAR ,\n");
			sbfSQL.append(" " + Common.COMMENTS_COUNT +" INTEGER ,\n");
			sbfSQL.append(" " + Common.DESCRIPTION +" VARCHAR,\n");
			sbfSQL.append(" " + Common.FAVOR +" INTEGER,\n");
			sbfSQL.append(" " + Common.FAVOR_TIMESTAMP +" INTEGER );\n");
			
			return sbfSQL.toString();
		}
		
		private String createTagList() {
			StringBuffer sbfSQL = new StringBuffer();
			sbfSQL.append(" CREATE TABLE tag_list( \n");
			sbfSQL.append(" tag VARCHAR NOT NULL PRIMARY KEY,\n");
			sbfSQL.append(" timestamp INTEGER NOT NULL DEFAULT 0,\n");
			sbfSQL.append(" sync_favor_time INTEGER NOT NULL DEFAULT 0);\n");
			
			return sbfSQL.toString();
		}
	}
}
